﻿using Dapper;
using Entity;
using IRepository;
using Microsoft.AspNetCore.Http;
using Microsoft.Extensions.Configuration;
using System;
using System.Collections.Generic;
using System.Data;
using Microsoft.Data.SqlClient;
using System.Linq;
using System.Security.Claims;
using System.Text;

namespace Repository
{
    public class NumberingRuleRepository : INumberingRuleRepository, IDisposable
    {
        private readonly IDbConnection _dbConnection;
        private readonly IHttpContextAccessor _httpContextAccessor;
        private bool _disposed = false;

        public NumberingRuleRepository(IConfiguration configuration, IHttpContextAccessor httpContextAccessor)
        {
            string connectionString = configuration.GetConnectionString("DefaultConnection");
            if (string.IsNullOrEmpty(connectionString))
            {
                throw new ArgumentException("数据库连接字符串 'DefaultConnection' 未配置");
            }
            _dbConnection = new SqlConnection(connectionString);
            _dbConnection.Open();
            _httpContextAccessor = httpContextAccessor;
        }

        /// <summary>
        /// 获取当前登录用户
        /// </summary>
        private string GetCurrentUser()
        {
            if (_httpContextAccessor?.HttpContext == null)
            {
                return "system";
            }

            try
            {
                // 从Session获取
                var session = _httpContextAccessor.HttpContext.Session;
                if (session != null)
                {
                    byte[] userBytes;
                    if (session.TryGetValue("LoginUserName", out userBytes))
                    {
                        var userName = Encoding.UTF8.GetString(userBytes);
                        if (!string.IsNullOrEmpty(userName))
                        {
                            Console.WriteLine($"【Repository】从Session获取到用户: {userName}");
                            return userName;
                        }
                    }
                }

                // 从User.Identity获取
                if (_httpContextAccessor.HttpContext.User?.Identity?.IsAuthenticated == true)
                {
                    var identityName = _httpContextAccessor.HttpContext.User.Identity.Name;
                    if (!string.IsNullOrEmpty(identityName))
                    {
                        Console.WriteLine($"【Repository】从Identity获取到用户: {identityName}");
                        return identityName;
                    }
                }

                // 从Claims获取
                var claim = _httpContextAccessor.HttpContext.User?.FindFirst(ClaimTypes.Name);
                if (claim != null && !string.IsNullOrEmpty(claim.Value))
                {
                    Console.WriteLine($"【Repository】从Claims获取到用户: {claim.Value}");
                    return claim.Value;
                }

                Console.WriteLine("【Repository】未获取到用户信息，使用默认值: system");
                return "system";
            }
            catch (Exception ex)
            {
                Console.WriteLine($"【Repository】获取用户信息异常: {ex.Message}");
                return "system";
            }
        }

        /// <summary>
        /// 根据目标表单名称获取最新规则
        /// </summary>
        public NumberingRule GetByTargetFormName(string targetFormName)
        {
            Console.WriteLine($"【GetByTargetFormName】开始查询，targetFormName: '{targetFormName}'");

            if (string.IsNullOrWhiteSpace(targetFormName))
                throw new ArgumentNullException(nameof(targetFormName), "目标表单名称不能为空");

            // 容错处理：如果传入的是数字，尝试按target_id查询
            if (int.TryParse(targetFormName, out int targetId))
            {
                Console.WriteLine($"检测到数字参数，按target_id查询: {targetId}");
                return GetByTargetId(targetId);
            }

            string sql = @"SELECT nr.*, tf.target_form
                  FROM sys_org_numbering_rules nr
                  INNER JOIN sys_target_form_table tf ON nr.target_id = tf.target_id
                  WHERE tf.target_form = @target_form_name 
                    AND tf.is_active = 1 
                    AND nr.is_delete = 0";

            Console.WriteLine($"执行编号规则SQL: {sql}");
            Console.WriteLine($"参数: target_form_name = '{targetFormName}'");

            try
            {
                var rule = _dbConnection.QueryFirstOrDefault<NumberingRule>(sql, new { target_form_name = targetFormName });

                if (rule != null)
                {
                    Console.WriteLine($"找到规则: ID={rule.rule_id}, 目标ID={rule.target_id}, 前缀={rule.number_prefix}, 表单={rule.target_form}");
                    rule.step = rule.step <= 0 ? 1 : rule.step;
                    rule.serial_number = rule.serial_number <= 0 ? 3 : rule.serial_number;
                }
                else
                {
                    Console.WriteLine($"未找到表单名称为「{targetFormName}」的编号规则");
                }

                return rule;
            }
            catch (SqlException ex)
            {
                Console.WriteLine($"SQL异常: 错误号={ex.Number}, 消息={ex.Message}");
                throw new Exception($"查询目标表单「{targetFormName}」的编号规则失败", ex);
            }
        }

        /// <summary>
        /// 根据ID获取编号规则 - 修复版本
        /// </summary>
        public NumberingRule GetById(int rule_id)
        {
            if (rule_id <= 0)
                throw new ArgumentOutOfRangeException(nameof(rule_id), "规则ID必须大于0");

            Console.WriteLine($"【GetById】开始查询规则ID: {rule_id}");

            string sql = @"SELECT nr.*, tf.target_form
                          FROM sys_org_numbering_rules nr
                          LEFT JOIN sys_target_form_table tf ON nr.target_id = tf.target_id
                          WHERE nr.rule_id = @rule_id AND nr.is_delete = 0";

            try
            {
                var rule = _dbConnection.QueryFirstOrDefault<NumberingRule>(sql, new { rule_id = rule_id });

                if (rule != null)
                {
                    Console.WriteLine($"【GetById】找到规则 - 前缀: {rule.number_prefix}, 目标表单: {rule.target_form}, 当前最大值: {rule.max_value}");

                    // 设置默认值
                    rule.step = rule.step <= 0 ? 1 : rule.step;
                    rule.serial_number = rule.serial_number <= 0 ? 3 : rule.serial_number;
                }
                else
                {
                    Console.WriteLine($"【GetById】未找到规则ID: {rule_id}");
                }

                return rule;
            }
            catch (SqlException ex)
            {
                Console.WriteLine($"【GetById】SQL异常: {ex.Message}");
                throw new Exception($"查询ID为{rule_id}的编号规则失败", ex);
            }
        }

        /// <summary>
        /// 保存规则（新增/更新）
        /// </summary>
        public int SaveRule(NumberingRule rule)
        {
            if (rule == null)
                throw new ArgumentNullException(nameof(rule));

            ValidateRule(rule);

            string currentUser = GetCurrentUser();

            try
            {
                if (rule.rule_id == 0)
                {
                    // 新增规则
                    rule.create_by = currentUser;
                    rule.Operator = currentUser;

                    string sql = @"INSERT INTO sys_org_numbering_rules 
                  (target_id, number_prefix, serial_number, step, time_rule,
                   number_production_rule, [operator], operation_time,
                   create_by, create_time, is_delete, max_value)
                  VALUES 
                  (@target_id, @number_prefix, @serial_number, @step, @time_rule,
                   @number_production_rule, @operator, @operation_time,
                   @create_by, @create_time, @is_delete, @max_value);
                  SELECT CAST(SCOPE_IDENTITY() AS INT);";

                    return _dbConnection.QuerySingle<int>(sql, new
                    {
                        target_id = rule.target_id,
                        number_prefix = rule.number_prefix,
                        serial_number = rule.serial_number,
                        step = rule.step,
                        time_rule = rule.time_rule,
                        number_production_rule = rule.number_production_rule,
                        @operator = rule.Operator,
                        operation_time = rule.operation_time,
                        create_by = rule.create_by,
                        create_time = rule.create_time,
                        is_delete = rule.is_delete,
                        max_value = rule.max_value
                    });
                }
                else
                {
                    // 更新规则
                    rule.update_by = currentUser;
                    rule.Operator = currentUser;

                    string sql = @"UPDATE sys_org_numbering_rules 
                  SET target_id = @target_id,
                      number_prefix = @number_prefix,
                      serial_number = @serial_number,
                      step = @step,
                      time_rule = @time_rule,
                      number_production_rule = @number_production_rule,
                      [operator] = @operator,
                      operation_time = @operation_time,
                      update_by = @update_by,
                      update_time = GETDATE(),
                      max_value = @max_value
                  WHERE rule_id = @rule_id
                  AND is_delete = 0";

                    var affectedRows = _dbConnection.Execute(sql, new
                    {
                        target_id = rule.target_id,
                        number_prefix = rule.number_prefix,
                        serial_number = rule.serial_number,
                        step = rule.step,
                        time_rule = rule.time_rule,
                        number_production_rule = rule.number_production_rule,
                        @operator = rule.Operator,
                        operation_time = rule.operation_time,
                        update_by = rule.update_by,
                        max_value = rule.max_value,
                        rule_id = rule.rule_id
                    });

                    if (affectedRows == 0)
                    {
                        throw new Exception($"未找到rule_id为{rule.rule_id}的规则或规则已被删除");
                    }

                    return rule.rule_id;
                }
            }
            catch (SqlException ex)
            {
                throw new Exception($"{(rule.rule_id == 0 ? "新增" : "更新")}编号规则失败: {ex.Message}", ex);
            }
        }

        public int GetMaxSerialNumber(int target_id, string number_prefix)
        {
            if (target_id <= 0)
                throw new ArgumentException("目标表单ID不能为空", nameof(target_id));

            if (string.IsNullOrWhiteSpace(number_prefix))
                throw new ArgumentException("编号前缀不能为空", nameof(number_prefix));

            string sql = @"SELECT ISNULL(MAX(serial_number), 0) 
                          FROM sys_org_numbering_rules 
                          WHERE target_id = @target_id 
                          AND number_prefix = @number_prefix 
                          AND is_delete = 0";

            try
            {
                return _dbConnection.ExecuteScalar<int>(sql, new
                {
                    target_id = target_id,
                    number_prefix = number_prefix
                });
            }
            catch (SqlException ex)
            {
                throw new Exception("获取最大流水号失败", ex);
            }
        }

        public int UpdateSerialNumber(int target_id, string number_prefix, int new_serial_number)
        {
            if (target_id <= 0)
                throw new ArgumentException("目标表单ID不能为空", nameof(target_id));

            if (string.IsNullOrWhiteSpace(number_prefix))
                throw new ArgumentException("编号前缀不能为空", nameof(number_prefix));

            if (new_serial_number < 0)
                throw new ArgumentOutOfRangeException(nameof(new_serial_number), "流水号不能为负数");

            string currentUser = GetCurrentUser();

            string sql = @"UPDATE sys_org_numbering_rules 
                          SET serial_number = @new_serial_number,
                              update_time = GETDATE(),
                              update_by = @current_user
                          WHERE target_id = @target_id 
                          AND number_prefix = @number_prefix 
                          AND is_delete = 0";

            try
            {
                return _dbConnection.Execute(sql, new
                {
                    new_serial_number = new_serial_number,
                    current_user = currentUser,
                    target_id = target_id,
                    number_prefix = number_prefix
                });
            }
            catch (SqlException ex)
            {
                throw new Exception("更新流水号失败", ex);
            }
        }
        
        public PaginationResult<NumberingRule> GetByConditions(NumberingRuleQueryParams queryParams)
        {
            queryParams.PageIndex = queryParams.PageIndex < 1 ? 1 : queryParams.PageIndex;
            queryParams.PageSize = queryParams.PageSize < 1 ? 10 : queryParams.PageSize;

            var result = new PaginationResult<NumberingRule>
            {
                PageIndex = queryParams.PageIndex,
                PageSize = queryParams.PageSize
            };

            var sqlBuilder = new StringBuilder(@"
                SELECT nr.*, tf.target_form
                FROM sys_org_numbering_rules nr
                LEFT JOIN sys_target_form_table tf ON nr.target_id = tf.target_id
                WHERE nr.is_delete = 0
            ");
            var countSqlBuilder = new StringBuilder(@"
                SELECT COUNT(1)
                FROM sys_org_numbering_rules nr
                WHERE nr.is_delete = 0
            ");
            var parameters = new DynamicParameters();

            if (queryParams.target_id.HasValue)
            {
                sqlBuilder.Append(" AND nr.target_id = @target_id");
                countSqlBuilder.Append(" AND nr.target_id = @target_id");
                parameters.Add("@target_id", queryParams.target_id.Value);
            }
            if (!string.IsNullOrWhiteSpace(queryParams.number_prefix))
            {
                sqlBuilder.Append(" AND nr.number_prefix LIKE @number_prefix");
                countSqlBuilder.Append(" AND nr.number_prefix LIKE @number_prefix");
                parameters.Add("@number_prefix", $"%{queryParams.number_prefix}%");
            }
            if (!string.IsNullOrWhiteSpace(queryParams.time_rule))
            {
                sqlBuilder.Append(" AND nr.time_rule LIKE @time_rule");
                countSqlBuilder.Append(" AND nr.time_rule LIKE @time_rule");
                parameters.Add("@time_rule", $"%{queryParams.time_rule}%");
            }
            if (queryParams.operation_time_start.HasValue)
            {
                sqlBuilder.Append(" AND nr.operation_time >= @operation_time_start");
                countSqlBuilder.Append(" AND nr.operation_time >= @operation_time_start");
                parameters.Add("@operation_time_start", queryParams.operation_time_start.Value);
            }
            if (queryParams.operation_time_end.HasValue)
            {
                sqlBuilder.Append(" AND nr.operation_time <= @operation_time_end");
                countSqlBuilder.Append(" AND nr.operation_time <= @operation_time_end");
                parameters.Add("@operation_time_end", queryParams.operation_time_end.Value);
            }

            try
            {
                result.TotalCount = _dbConnection.QuerySingle<int>(countSqlBuilder.ToString(), parameters);

                sqlBuilder.Append(" ORDER BY nr.operation_time DESC")
                          .Append(" OFFSET @skip_count ROWS FETCH NEXT @page_size ROWS ONLY");
                parameters.Add("@skip_count", (queryParams.PageIndex - 1) * queryParams.PageSize);
                parameters.Add("@page_size", queryParams.PageSize);

                result.Data = _dbConnection.Query<NumberingRule>(sqlBuilder.ToString(), parameters).AsList();
                return result;
            }
            catch (SqlException ex)
            {
                throw new Exception("多条件分页查询编号规则失败", ex);
            }
        }

        private void ValidateRule(NumberingRule rule)
        {
            if (rule.rule_id > 0 && rule.target_id <= 0)
                throw new ArgumentException("目标表单不能为空", nameof(rule.target_id));

            if (string.IsNullOrWhiteSpace(rule.number_prefix))
                throw new ArgumentException("编号前缀不能为空", nameof(rule.number_prefix));

            if (rule.serial_number <= 0)
                throw new ArgumentException("流水号位数必须大于0", nameof(rule.serial_number));

            if (rule.step <= 0)
                throw new ArgumentException("步长必须大于0", nameof(rule.step));

            if (rule.rule_id == 0 && string.IsNullOrWhiteSpace(rule.create_by))
                throw new ArgumentException("创建人不能为空", nameof(rule.create_by));
        }
        
        public List<NumberingRule> GetByIds(List<int> ids)
        {
            if (ids == null || !ids.Any())
            {
                return new List<NumberingRule>();
            }

            string sql = @"SELECT nr.*, tf.target_form
                          FROM sys_org_numbering_rules nr
                          LEFT JOIN sys_target_form_table tf ON nr.target_id = tf.target_id
                          WHERE nr.rule_id IN @ids AND nr.is_delete = 0";

            try
            {
                return _dbConnection.Query<NumberingRule>(sql, new { ids = ids }).AsList();
            }
            catch (SqlException ex)
            {
                throw new Exception("根据ID列表查询编号规则失败", ex);
            }
        }

        /// <summary>
        /// 带更新锁查询规则（修复SQL Server锁定语法）
        /// </summary>
        public NumberingRule GetRuleWithLock(int rule_id)
        {
            return GetRuleWithLock(rule_id, null);
        }
        // 带锁查询规则（接收事务参数）
        public NumberingRule GetRuleWithLock(int rule_id, IDbTransaction transaction = null)
        {
            if (rule_id <= 0)
                throw new ArgumentOutOfRangeException(nameof(rule_id), "规则ID必须大于0");

            // 关键修复：使用SQL Server支持的UPDLOCK表提示，替换FOR UPDATE（适配SQL Server语法）
            Console.WriteLine($"【GetRuleWithLock】开始查询 rule_id={rule_id}");
            string sql = @"SELECT nr.*, tf.target_form
                          FROM sys_org_numbering_rules nr WITH (UPDLOCK)  -- 加更新锁，防止并发更新冲突
                          LEFT JOIN sys_target_form_table tf ON nr.target_id = tf.target_id
                          WHERE nr.rule_id = @rule_id 
                            AND nr.is_delete = 0;  -- 确保只查询未删除的规则";

            try
            {
                var rule = _dbConnection.QueryFirstOrDefault<NumberingRule>(sql, new { rule_id = rule_id }, transaction);
                Console.WriteLine($"【GetRuleWithLock】查询结果：{(rule != null ? rule.rule_id.ToString() : "null")}");

                // 执行“空更新”来获取行锁（SQL Server通过更新操作隐式加锁）
                if (rule != null)
                {
                    string lockSql = @"UPDATE sys_org_numbering_rules 
                              SET last_update_time = @last_update_time
                              WHERE rule_id = @rule_id
                              AND is_delete = 0";
                    _dbConnection.Execute(lockSql, new { last_update_time = DateTime.Now, rule_id = rule.rule_id }, transaction);
                }

                return rule;
            }
            catch (SqlException ex)
            {
                Console.WriteLine($"【GetRuleWithLock】SQL异常：{ex.Message}，SQL：{sql}");
                throw new Exception($"查询ID为{rule_id}的编号规则失败", ex);
            }
        }

        // 更新最大值和时间（接收事务参数）
        public bool UpdateMaxValueAndTime(int rule_id, int newMaxValue, DateTime last_generation_time, string @operator, IDbTransaction transaction = null)
        {
            if (rule_id <= 0)
                throw new ArgumentOutOfRangeException(nameof(rule_id), "规则ID必须大于0");

            if (string.IsNullOrWhiteSpace(@operator))
            {
                @operator = GetCurrentUser();
            }

            string sql = @"UPDATE sys_org_numbering_rules
                      SET max_value = @newMaxValue,
                          last_update_time = @last_generation_time, -- 仅保留一处赋值（使用参数）
                          update_by = @operator,
                          update_time = GETDATE()
                      WHERE rule_id = @rule_id
                      AND is_delete = '0';";

            try
            {
                int affectedRows = _dbConnection.Execute(sql, new
                {
                    newMaxValue = newMaxValue,
                    last_generation_time = last_generation_time,
                    @operator = @operator,
                    rule_id = rule_id
                }, transaction);

                return affectedRows > 0;
            }
            catch (SqlException ex)
            {
                throw new Exception($"更新规则ID为{rule_id}的最大值和时间失败", ex);
            }
        }

        /// <summary>
        /// 更新最大值和时间 - 修复版本
        /// </summary>
        public bool UpdateMaxValueAndTime(int rule_id, int new_max_value, DateTime last_generation_time, string operator_name)
        {
            if (rule_id <= 0)
                throw new ArgumentOutOfRangeException(nameof(rule_id), "规则ID必须大于0");

            if (string.IsNullOrWhiteSpace(operator_name))
            {
                operator_name = GetCurrentUser();
            }

            Console.WriteLine($"【UpdateMaxValueAndTime】开始更新规则ID={rule_id}，新流水号={new_max_value}");

            // 关键修复：使用正确的字段名
            string sql = @"UPDATE sys_org_numbering_rules 
                  SET max_value = @new_max_value,
                      update_by = @operator_name,
                      update_time = GETDATE()
                  WHERE rule_id = @rule_id
                  AND is_delete = 0";

            try
            {
                int affectedRows = _dbConnection.Execute(sql, new
                {
                    new_max_value = new_max_value,
                    operator_name = operator_name,
                    rule_id = rule_id
                });

                Console.WriteLine($"【UpdateMaxValueAndTime】更新完成，影响行数={affectedRows}");

                return affectedRows > 0;
            }
            catch (SqlException ex)
            {
                Console.WriteLine($"【UpdateMaxValueAndTime】SQL异常：错误号={ex.Number}，消息={ex.Message}");
                throw new Exception($"更新规则ID为{rule_id}的最大值失败", ex);
            }
        }

        public NumberingRule GetByTargetAndPrefix(int target_id, string number_prefix)
        {
            string sql = "SELECT * FROM sys_org_numbering_rules WHERE target_id = @target_id AND number_prefix = @number_prefix AND is_delete = 0";
            return _dbConnection.QueryFirstOrDefault<NumberingRule>(sql, new { target_id = target_id, number_prefix = number_prefix });
        }

        /// <summary>
        /// 同时添加目标表单和编号规则
        /// </summary>
        public int AddTargetAndRule(TargetForm targetForm, NumberingRule numberingRule)
        {
            if (targetForm == null)
                throw new ArgumentNullException(nameof(targetForm));
            if (numberingRule == null)
                throw new ArgumentNullException(nameof(numberingRule));

            Console.WriteLine($"【AddTargetAndRule】开始处理，目标表单: {targetForm.target_form}");

            // 验证目标表单
            ValidateTargetForm(targetForm);

            // 验证编号规则
            if (string.IsNullOrWhiteSpace(numberingRule.number_prefix))
                throw new ArgumentException("编号前缀不能为空", nameof(numberingRule.number_prefix));
            if (numberingRule.serial_number <= 0)
                throw new ArgumentException("流水号必须大于0", nameof(numberingRule.serial_number));
            if (numberingRule.step <= 0)
                throw new ArgumentException("步长必须大于0", nameof(numberingRule.step));

            string currentUser = GetCurrentUser();
            int newRuleId = 0;
            IDbTransaction transaction = null;

            try
            {
                transaction = _dbConnection.BeginTransaction();

                // 1. 检查目标表单是否已存在
                string checkFormSql = "SELECT COUNT(1) FROM sys_target_form_table WHERE target_form = @target_form AND is_active = 1";
                int formExists = _dbConnection.ExecuteScalar<int>(checkFormSql, new { target_form = targetForm.target_form }, transaction);

                if (formExists > 0)
                {
                    throw new Exception($"目标表单名称 '{targetForm.target_form}' 已存在");
                }

                // 2. 获取下一个可用的target_id
                string getMaxIdSql = "SELECT ISNULL(MAX(target_id), 0) + 1 FROM sys_target_form_table WITH (UPDLOCK, HOLDLOCK)";
                int newTargetId = _dbConnection.ExecuteScalar<int>(getMaxIdSql, transaction: transaction);

                // 3. 新增目标表单
                string targetSql = @"
            INSERT INTO sys_target_form_table (target_id, target_form, is_active, creation_time)
            VALUES (@target_id, @target_form, @is_active, GETDATE())";

                _dbConnection.Execute(targetSql, new
                {
                    target_id = newTargetId,
                    target_form = targetForm.target_form?.Trim(),
                    is_active = targetForm.is_active
                }, transaction);

                Console.WriteLine($"【AddTargetAndRule】目标表单创建成功，ID: {newTargetId}");

                // 4. 设置编号规则完整数据
                numberingRule.target_id = newTargetId;
                numberingRule.Operator = currentUser;
                numberingRule.operation_time = DateTime.Now;
                numberingRule.create_by = currentUser;
                numberingRule.create_time = DateTime.Now;
                numberingRule.is_delete = 0;
                numberingRule.max_value = 0;

                // 确保时间规则有默认值
                if (string.IsNullOrEmpty(numberingRule.time_rule))
                {
                    numberingRule.time_rule = "YYYYMMDD";
                }

                // 确保生成规则有值
                if (string.IsNullOrEmpty(numberingRule.number_production_rule))
                {
                    numberingRule.number_production_rule = GenerateDefaultProductionRule(numberingRule);
                }

                // 5. 新增编号规则
                string ruleSql = @"
            INSERT INTO sys_org_numbering_rules 
            (target_id, number_prefix, serial_number, step, time_rule,
             number_production_rule, [operator], operation_time,
             create_by, create_time, is_delete, max_value)
            VALUES 
            (@target_id, @number_prefix, @serial_number, @step, @time_rule,
             @number_production_rule, @operator, @operation_time,
             @create_by, @create_time, @is_delete, @max_value);
            SELECT CAST(SCOPE_IDENTITY() AS INT);";

                newRuleId = _dbConnection.QuerySingle<int>(ruleSql, new
                {
                    target_id = numberingRule.target_id,
                    number_prefix = numberingRule.number_prefix?.Trim(),
                    serial_number = numberingRule.serial_number,
                    step = numberingRule.step,
                    time_rule = numberingRule.time_rule,
                    number_production_rule = numberingRule.number_production_rule,
                    @operator = numberingRule.Operator,
                    operation_time = numberingRule.operation_time,
                    create_by = numberingRule.create_by,
                    create_time = numberingRule.create_time,
                    is_delete = numberingRule.is_delete,
                    max_value = numberingRule.max_value
                }, transaction);

                Console.WriteLine($"【AddTargetAndRule】编号规则创建成功，ID: {newRuleId}");

                transaction.Commit();
                Console.WriteLine($"【AddTargetAndRule】事务提交成功");
                return newRuleId;
            }
            catch (Exception ex)
            {
                Console.WriteLine($"【AddTargetAndRule】发生异常: {ex.Message}");
                transaction?.Rollback();
                throw new Exception($"同时新增目标表单和编号规则失败: {ex.Message}", ex);
            }
            finally
            {
                transaction?.Dispose();
            }
        }

        /// <summary>
        /// 生成默认的编号规则预览
        /// </summary>
        private string GenerateDefaultProductionRule(NumberingRule rule)
        {
            string timePart = DateTime.Now.ToString("yyyyMMdd");
            var serialPart = rule.step.ToString().PadLeft(rule.serial_number, '0');
            return $"{rule.number_prefix}{timePart}{serialPart}";
        }

        public bool DeleteRule(int rule_id, string current_user)
        {
            if (rule_id <= 0)
                throw new ArgumentOutOfRangeException(nameof(rule_id), "规则ID必须大于0");

            if (string.IsNullOrWhiteSpace(current_user))
            {
                current_user = GetCurrentUser();
            }

            string sql = @"UPDATE sys_org_numbering_rules 
                          SET is_delete = 1,
                              update_by = @current_user,
                              update_time = GETDATE()
                          WHERE rule_id = @rule_id
                          AND is_delete = 0";

            try
            {
                var affectedRows = _dbConnection.Execute(sql, new
                {
                    rule_id = rule_id,
                    current_user = current_user
                });

                return affectedRows > 0;
            }
            catch (SqlException ex)
            {
                throw new Exception($"删除规则ID为{rule_id}的编号规则失败", ex);
            }
        }

        private void ValidateTargetForm(TargetForm targetForm)
        {
            if (string.IsNullOrWhiteSpace(targetForm.target_form))
                throw new ArgumentException("目标表单名称不能为空", nameof(targetForm.target_form));
            if (targetForm.is_active < 0)
                throw new ArgumentException("is_active必须为非负数", nameof(targetForm.is_active));
        }

        public int GetMaxSerialNumber(string target_form, string number_prefix)
        {
            var target_id = GetTargetIdByName(target_form);
            if (target_id <= 0)
                throw new KeyNotFoundException($"未找到名称为'{target_form}'的目标表单");

            return GetMaxSerialNumber(target_id, number_prefix);
        }

        public int UpdateSerialNumber(string target_form, string number_prefix, int new_serial_number)
        {
            var target_id = GetTargetIdByName(target_form);
            if (target_id <= 0)
                throw new KeyNotFoundException($"未找到名称为'{target_form}'的目标表单");

            return UpdateSerialNumber(target_id, number_prefix, new_serial_number);
        }

        private int GetTargetIdByName(string target_form_name)
        {
            if (string.IsNullOrWhiteSpace(target_form_name))
                return 0;

            string sql = "SELECT target_id FROM sys_target_form_table WHERE target_form = @target_form AND is_active = 1";
            return _dbConnection.QueryFirstOrDefault<int?>(sql, new { target_form = target_form_name }) ?? 0;
        }

        public IDbConnection GetDbConnection()
        {
            return _dbConnection;
        }

        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }

        protected virtual void Dispose(bool disposing)
        {
            if (!_disposed)
            {
                if (disposing)
                {
                    if (_dbConnection != null && _dbConnection.State != ConnectionState.Closed)
                    {
                        _dbConnection.Close();
                        _dbConnection.Dispose();
                    }
                }
                _disposed = true;
            }
        }

        ~NumberingRuleRepository()
        {
            Dispose(false); 
        }

        public NumberingRule GetByTargetId(int targetId)
        {
            string sql = "SELECT * FROM sys_org_numbering_rules WHERE target_id = @TargetId AND is_delete = 0";
            return _dbConnection.QueryFirstOrDefault<NumberingRule>(sql, new { TargetId = targetId });
        }
    }
}